﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Dapper;
using DapperExtensions;
using DapperExtensions.Predicate;
using MySql.Data.MySqlClient;

namespace FileStorage.Common
{
    public class DapperHelper : IDisposable
    {
        private readonly DbConnection _dbConnection;

        public DapperHelper()
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            _dbConnection = new MySqlConnection(SystemConfig.MySqlReadonlyConnection);
            _dbConnection.Open();
        }

        public static DbConnection GetDbConnection()
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            DbConnection dbConnection = new MySqlConnection(SystemConfig.MySqlReadonlyConnection);
            if (dbConnection.State != ConnectionState.Open)
                dbConnection.Open();
            return dbConnection;
        }

        public IEnumerable<dynamic> Query(string sql, object param)
        {
            return _dbConnection.Query(sql, param);
        }

        public IEnumerable<T> Query<T>(string sql, object param = null)
        {
            var list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(sql))
            {
                list = _dbConnection.Query<T>(sql, param);
            }
            return list;
        }

        public IEnumerable<T> QueryByPredicate<T>(object predicate = null, IList<ISort> sort = null, DbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            IEnumerable<T> list = default;
            if (predicate != null)
            {
                list = _dbConnection.GetList<T>(predicate, sort, transaction, commandTimeout, buffered);
            }
            return list;
        }
        public IEnumerable<T> QueryStored<T>(string storedName, object param)
        {
            var list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(storedName))
            {
                list = _dbConnection.Query<T>(storedName, param, commandType: CommandType.StoredProcedure);
            }
            return list;
        }
        public T FirstOrDefault<T>(string sql, object param = null)
        {
            var model = default(T);
            if (!string.IsNullOrEmpty(sql))
            {
                model = _dbConnection.Query<T>(sql, param).FirstOrDefault();
            }
            return model;
        }
        public T LastOrDefault<T>(string sql, object param)
        {
            var model = default(T);
            if (!string.IsNullOrEmpty(sql))
            {
                model = _dbConnection.Query<T>(sql, param).LastOrDefault();
            }
            return model;
        }

        public Tuple<IEnumerable<T1>, IEnumerable<T2>> Query<T1, T2>(string sql, object param)
        {
            IEnumerable<T1> item1 = null; IEnumerable<T2> item2 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (var multi = _dbConnection.QueryMultiple(sql, param))
                {
                    item1 = multi.Read<T1>();
                    item2 = multi.Read<T2>();
                }
            }
            return Tuple.Create(item1, item2);
        }
        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> Query<T1, T2, T3>(string sql, object param)
        {
            IEnumerable<T1> item1 = null; IEnumerable<T2> item2 = null; IEnumerable<T3> item3 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (var multi = _dbConnection.QueryMultiple(sql, param))
                {
                    item1 = multi.Read<T1>();
                    item2 = multi.Read<T2>();
                    item3 = multi.Read<T3>();
                }
            }
            return Tuple.Create(item1, item2, item3);
        }
        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>> Query<T1, T2, T3, T4>(string sql, object param)
        {
            IEnumerable<T1> item1 = null; IEnumerable<T2> item2 = null; IEnumerable<T3> item3 = null; IEnumerable<T4> item4 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (var multi = _dbConnection.QueryMultiple(sql, param))
                {
                    item1 = multi.Read<T1>();
                    item2 = multi.Read<T2>();
                    item3 = multi.Read<T3>();
                    item4 = multi.Read<T4>();
                }
            }
            return Tuple.Create(item1, item2, item3, item4);
        }
        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>, IEnumerable<T5>> Query<T1, T2, T3, T4, T5>(string sql, object param)
        {
            IEnumerable<T1> item1 = null; IEnumerable<T2> item2 = null; IEnumerable<T3> item3 = null; IEnumerable<T4> item4 = null; IEnumerable<T5> item5 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (var multi = _dbConnection.QueryMultiple(sql, param))
                {
                    item1 = multi.Read<T1>();
                    item2 = multi.Read<T2>();
                    item3 = multi.Read<T3>();
                    item4 = multi.Read<T4>();
                    item5 = multi.Read<T5>();
                }
            }
            return Tuple.Create(item1, item2, item3, item4, item5);
        }

        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>, IEnumerable<T4>, IEnumerable<T5>, IEnumerable<T6>> Query<T1, T2, T3, T4, T5, T6>(string sql, object param)
        {
            IEnumerable<T1> item1 = null; IEnumerable<T2> item2 = null; IEnumerable<T3> item3 = null; IEnumerable<T4> item4 = null; IEnumerable<T5> item5 = null; IEnumerable<T6> item6 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (var multi = _dbConnection.QueryMultiple(sql, param))
                {
                    item1 = multi.Read<T1>();
                    item2 = multi.Read<T2>();
                    item3 = multi.Read<T3>();
                    item4 = multi.Read<T4>();
                    item5 = multi.Read<T5>();
                    item6 = multi.Read<T6>();
                }
            }
            return Tuple.Create(item1, item2, item3, item4, item5, item6);
        }

        public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map)
        {
            return _dbConnection.Query(sql, map);
        }

        public bool ExecuteSql(string sql, object param)
        {
            return _dbConnection.Execute(sql, param) > 0;
        }

        public bool ExecuteWithTransaction(DbTransaction transaction, string sql, object param)
        {
            return _dbConnection.Execute(sql, param, transaction) > 0;
        }

        public void Dispose()
        {
            if (_dbConnection != null)
            {
                bool isClosed = _dbConnection.State == ConnectionState.Closed;
                if (!isClosed)
                {
                    _dbConnection.Close();
                    _dbConnection.Dispose();
                }
            }
        }
        public dynamic Insert<T>(T entity, DbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            return _dbConnection.Insert(entity, transaction, commandTimeout);
        }

        public bool Update<T>(T entity, DbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            return _dbConnection.Update(entity, transaction, commandTimeout);
        }

        public T Get<T>(object id, DbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            return _dbConnection.Get<T>(id, transaction, commandTimeout);
        }
        public bool Delete<T>(object predicate, DbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            return _dbConnection.Delete<T>(predicate, transaction, commandTimeout);
        }
        public static IEnumerable<T> GetList<T>(object predicate = null, IList<ISort> sort = null, DbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            using (var conn = new MySqlConnection(SystemConfig.MySqlReadonlyConnection))
            {
                conn.Open();
                return conn.GetList<T>(predicate, sort, transaction, commandTimeout, buffered);
            }
        }
        public static void Insert<T>(IEnumerable<T> entity, DbTransaction transaction = null, int? commandTimeout = null, string sql = null, object param = null) where T : class
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            using (var conn = new MySqlConnection(SystemConfig.MySqlReadonlyConnection))
            {
                conn.Open();
                if (!string.IsNullOrEmpty(sql))
                {
                    conn.Execute(sql, param);
                }
                conn.Insert(entity, transaction, commandTimeout);
                conn.Close();
            }
        }
        public static dynamic InsertEntity<T>(T entity, DbTransaction transaction = null, int? commandTimeout = null, string sql = null, object param = null) where T : class
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            using (var conn = new MySqlConnection(SystemConfig.MySqlReadonlyConnection))
            {
                conn.Open();
                if (!string.IsNullOrEmpty(sql))
                {
                    conn.Execute(sql, param);
                }
                var result = conn.Insert(entity, transaction, commandTimeout);
                conn.Close();
                return result;
            }
        }
        public static bool Execute(string sql, object param = null)
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            using (var conn = new MySqlConnection(SystemConfig.MySqlReadonlyConnection))
            {
                conn.Open();
                return conn.Execute(sql, param) > 0;
            }
        }
        public static IEnumerable<T> QueryObjects<T>(string sql, object param = null)
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
            using (var conn = new MySqlConnection(SystemConfig.MySqlReadonlyConnection))
            {
                conn.Open();
                return conn.Query<T>(sql, param);
            }

        }
        public static T QueryObject<T>(string sql, object param = null)
        {
            return QueryObjects<T>(sql, param).FirstOrDefault();
        }
        public static bool HardDeleteById<T>(object id)
        {
            return Execute($"delete from {typeof(T).Name} where id = @id", new { id });
        }

        public static T GetById<T>(long id) where T : class
        {
            using (var cn = new DapperHelper())
            {
                return cn.Get<T>(id);
            }
        }
    }
}
